Methods and devices for dynamic filter pushdown for massive parallel processing databases on cloud

ABSTRACT

A method for dynamic filter pushdown for massive parallel processing databases on the cloud, including acquiring one or more filters corresponding to a query, acquiring statistics information of one or more database tables, determining a selectivity of the one or more database tables based on the statistics information, determining whether the selectivity satisfies a threshold condition, and pushing down the one or more filters to the one or more database tables based on the determination of whether the selectivity satisfies a threshold condition.

BACKGROUND

Increasing the sizes of database and allowing for big data analysis isunlocking the potential of data. Many companies have developed databasescanning techniques that allow for quicker and less computationallyintensive scanning of large amounts of data. The inaccuracy of currentselectivity estimations, however, may lead to unnecessary processing,decreased efficiency, and increases computational times.

Additionally, the lack of support for different storage types in hashjoin and semi-join operations leads to an inability to perform theseoperations on large numbers of databases which may have variousdifferent storage types.

Furthermore, the lack of an effective scheme for dynamically pushingdown filters causes redundancy and unnecessary computation, leading todecreased efficiency and increased computational times.

SUMMARY

The present disclosure provides a method for dynamic filter pushdown formassive parallel processing databases on the cloud. The method includesacquiring one or more filters corresponding to a query, acquiringstatistics information of one or more database tables, determining aselectivity of the one or more database tables based on the statisticsinformation, determining whether the selectivity satisfies a thresholdcondition, and pushing down the one or more filters to the one or moredatabase tables based on the determination of whether the selectivitysatisfies a threshold condition.

Consistent with some embodiments, the present disclosure also provides adevice. The device includes a memory configured to store a set ofinstructions and a processor configured to execute the set ofinstructions to cause the device to acquire one or more filterscorresponding to a query, acquire statistics information of one or moredatabase tables, determine a selectivity of the one or more databasetables based on the statistics information, determine whether theselectivity satisfies a threshold condition, and push down the one ormore filters to the one or more database tables based on thedetermination of whether the selectivity satisfies a thresholdcondition.

Consistent with some embodiments, the present disclosure also provides adatabase system. The database system includes at least one front nodeconfigured to receive one or more query associated with one or morefilters and at least one compute node coupled to the at least one frontnode, the at least one compute node configured to acquire one or morefilters corresponding to a query, acquire statistics information of oneor more database tables, determine a selectivity of the one or moredatabase tables based on the statistics information, determine whetherthe selectivity satisfies a threshold condition, and push down the oneor more filters to the one or more database tables based on thedetermination of whether the selectivity satisfies a thresholdcondition.

Additional features and advantages of the disclosed embodiments will beset forth in part in the following description, and in part will beapparent from the description, or may be learned by practice of theembodiments. The features and advantages of the disclosed embodimentsmay be realized and attained by the elements and combinations set forthin the claims.

It is to be understood that both the foregoing general description andthe following detailed description are exemplary and explanatory onlyand are not restrictive of the disclosed embodiments, as claimed.

BRIEF DESCRIPTION OF THE DRAWINGS

The accompanying drawings, which are incorporated in and constitute apart of this specification, illustrate embodiments consistent with theinvention and, together with the description, explain the principles ofthe invention.

FIG. 1 illustrates a block diagram of a conventional mechanism fordynamic filter pushdown.

FIG. 2 illustrates a block diagram of an exemplary system environment,consistent with some embodiments of the disclosure.

FIG. 3 illustrates a block diagram of an exemplary mechanism for dynamicfilter pushdown for massive parallel processing databases on cloud,consistent with some embodiments of this disclosure.

FIG. 4 is a flowchart of an exemplary method for dynamic filterpushdown, consistent with some embodiments of the disclosure.

FIG. 5 is another flowchart of an exemplary method for dynamic filterpushdown, consistent with some embodiments of the disclosure.

FIG. 6 is another flowchart of an exemplary method for dynamic filterpushdown, consistent with some embodiments of the disclosure.

FIG. 7 is another flowchart of an exemplary method for dynamic filterpushdown, consistent with some embodiments of the disclosure.

DESCRIPTION OF THE EMBODIMENTS

Reference will now be made in detail to exemplary embodiments, examplesof which are illustrated in the accompanying drawings. The followingdescription refers to the accompanying drawings in which the samenumbers in different drawings represent the same or similar elementsunless otherwise represented. The implementations set forth in thefollowing description of exemplary embodiments do not represent allimplementations consistent with the invention. Instead, they are merelyexamples of apparatuses and methods consistent with aspects related tothe invention as recited in the appended claims.

FIG. 1 illustrates a block diagram of a conventional mechanism fordynamic filter pushdown 100. As shown in FIG. 1, conventional mechanismsfor dynamic filter pushdown include a front node 102, a compute node104, and a storage node 106.

As show in FIG. 1, many conventional mechanisms include a front node 102that performs parsing and query optimization based on a received queryfrom a client device. Additionally, front node 102 performs planfragmentation, analyzes the plan, and decides filter columns based onthe query. Statistical information relating to the database tablesinvolved in the operations is utilized in front node 102 rather than thecompute node 104. This leads to less accurate selectivity estimationsand lower quality dynamic filters. Consequently, the serversimplementing these mechanisms suffer heightened input/output costs,decreased efficiency, and high computational times.

As shown in FIG. 1, conventional mechanisms for dynamic filter pushdowninclude compute node 104. For a join operation, during the building ofthe hash table from the small table, filters are collected based on thejoin predicates. Afterwards, before probing the big table, the filterscollected based on the join predicates are pushed down to the scanningof the big table. During the scanning of the big table, all partitionsof the big table are scanned. By not removing the partitions of the bigtable that do not correspond with a dynamic filter, the serversimplementing these mechanisms suffer heightened input/output costs,decreased efficiency, and high computational times.

As shown in FIG. 1, conventional mechanisms for dynamic filter pushdowninclude storage node 106 having a big table and a small table. For ajoin operator, a big table is defined as the table with the larger sizebased on the number of rows and columns from each table that are part ofthe result set. Similarly, a small table is defined as the table withthe smaller size based on the number of rows and columns from each tablethat are part of the result set. In conventional mechanisms, only onedefault storage type is used for the database tables. This leads to aninability to perform hash join and semi join operations on databasetables of different storage types.

Embodiments of the present disclosure are directed to methods anddevices for dynamic filter pushdown for massive parallel processingdatabases on cloud. For example and without limitation, embodiments ofthe present disclosure use statistical information at the compute nodeto determine selectivity of the big table and thus whether to pushdowndynamic filters. Additionally, embodiments of the present disclosuresupport various heterogeneous storage types for pushing down dynamicfilters. Furthermore, embodiments of the present disclosure allow forpartitioning of the dynamic filters based on the partition scheme of thebig table and pruning the partitions of the big table that do notcorrespond with any dynamic filters.

FIG. 2 illustrates a block diagram of an exemplary system for dynamicfilter pushdown for massive parallel processing databases on cloud 200,according to embodiments of the disclosure. Dynamic filter pushdownsystem 200 may include a server 202, network 212, external server 214,client devices 216, and peripheral devices 218.

As illustrated in FIG. 2, server 202 may be connected to network 212through a communication interface 208. In some embodiments, server 202may be connected to client devices 216 and one or more external servers214 through network 212. Additionally, in some embodiments, server 202may be connected to peripheral devices 218 through bus 210.

FIG. 3 illustrates a block diagram of an exemplary mechanism for dynamicfilter pushdown for massive parallel processing databases on cloud 300,consistent with some embodiments of this disclosure. As shown in FIG. 3,the exemplary mechanism may include a front node 302, a compute node304, a storage node 306, and a MetaData Service module 308. Front node302 and compute node 304 may be implemented on a server, e.g. server202. In some embodiments, only compute node 304 is implemented on asingle server (e.g. server 202) and front node 302 may be implementedelsewhere, such as one of the one or more external servers 214.

As shown in FIG. 3, front node 302 performs parsing and queryoptimization and plan fragmentation. Additionally, front node 302analyzes the plan and decides filter columns based on the query.

As shown in FIG. 3, MetaData Service module 308 obtains and periodicallyupdates statistics information of the database tables. In someembodiments, statistics information may include how many distinct valuesare in each table and how many rows of each table satisfy the criteriaof the query. In some embodiments, the MetaData Service module 308collects statistics information for different columns of each table.

As shown in FIG. 3, compute node 304 contains a TableStatisticsCache.The TableStatisticsCache caches statistics information of the databasetables. The TableStatisticsCache caches the statistics information thatis obtained and periodically updated by the MetaData Service module 308.In some embodiments, TableStatisticsCache maintains a histogramcorresponding with the statistics information obtained and periodicallyupdated by the MetaData Service module 308.

As shown in FIG. 3, storage node 306 contains a big table and a smalltable, which may be stored on various types of heterogeneous storagetypes, such as OSS, Table Store, HBase, HDFS, PostgreSQL, MySQL, andothers. According to the mechanism disclosed above, the compute node 304may pushdown the dynamic filters to the storage node 306 throughdifferent methods depending on the storage type of the big table. Forexample, for MySQL and PostgreSQL, the dynamic filters may be pusheddown using SQL with in list. Additionally, for HBase, OSS, andTableStore, the dynamic filters may be pushed down using the appropriateapplication programming interface (API) such as HBase API, SelectObjectAPI, or TableStore API. In some embodiments, other methods of pushingdown the dynamic filters are used based on the intended storage type ofthe big table. Because the dynamic filters are pushed down, fewer tuplesare fetched from storage node 306 and the input/output cost of fetchingis therefore reduced.

In some embodiments, for a join or semi join operation, during thebuilding of the hash table from the small table, dynamic filters arecollected based on the join predicates. Afterwards, the selectivity ofthe big table is calculated based on the dynamic filters and thestatistics information. In some embodiments, the selectivity of the bigtable may be calculated by also using the histogram maintained byTableStatisticsCache.

In some embodiments, the dynamic filters are only pushed down to thescanning of the big table based on whether the selectivity of the bigtable satisfies a threshold condition. For example, if the selectivityof the big table is less than 20%, where 20% represents the percent ofthe big table to be read, then the dynamic filters are pushed down tothe scanning of the big table. If the selectivity of the big table ishigher than 20%, however, then compute node 304 can continue with aregular hash join or semi join operation. Because the dynamic filterscollected previously are accurate, the estimated selectivity is moreprecise.

In some embodiments, if it is determined that the selectivity of the bigtable satisfies the threshold condition and after the dynamic filtersare pushed down to the scanning of the big table, compute node 304probes the big table. This dynamic filter pushdown allows for evengreater reduction in the input/output costs for fetching data from thebig table than conventional methods.

FIG. 4 is a flowchart of an exemplary method 400 for dynamic filterpushdown for massive parallel processing databases on cloud, consistentwith some embodiments of this disclosure. The exemplary method 400 maybe performed by a server (e.g., server 202 of FIG. 2 or compute node 304of FIG. 3).

In step 402, the server acquires dynamic filters during hash tablebuilding for a hash join or semi-join operation. For example, the servermay collect dynamic filters based on the join predicate of the hash joinor semi join operation. In some embodiments, the join predicate includesimplied predicates derived from the initial query.

In step 404, the server acquires statistical information of the bigtable from the TableStatisticsCache. For example, the server may obtaina histogram of collected rows from the small table based on thestatistics information obtained and periodically updated by theMetaDataService module of FIG. 3.

In step 406, the server determines the selectivity of the big tablebased on the statistical information and the dynamic filters. Forexample, the server may determine the percentage of the big table to beread for the present query based on the statistical information of thebig table acquired in step 404 and the dynamic filters acquired in step402.

In step 408, the server determines whether selectivity satisfies athreshold condition. For example, the server may determine whether theselectivity of the big table determined in step 406 is less than 20%,where 20% represents the percent of the big table to be read. In someembodiments, the threshold condition is prestored in memory, such as aconfiguration variable stored in one of the one or more storage devices206 of FIG. 2. If the server determines that the selectivity does notsatisfy the threshold condition, the server will then execute step 410,in which the server continues with a regular hash join or semi-joinoperation. For example, the server may not pushdown any dynamic filtersand instead continue probing and then scanning the big table.

On the other hand, if the server determines that the selectivitysatisfies the threshold condition, the server can then execute step 412.In step 412, the server passes the filters to big table scanning on theprobe side of join. For example, the server may pushdown the dynamicfilters from the small table to the big table in order to reduce thenumber of tuples fetched and thereby reduce input/output cost.

In step 414, the server chooses the API for tuple fetching according tobig table storage type. For example, based on the different possiblestorage type options of the big table, the server can choose the APIaccording to that storage type. In some embodiments, the server may notchoose an API but rather determine to use SQL with in list for thedynamic filter pushdown.

In step 416, the server determines whether the join predicate column isa partition column of the big table. For example, the server maydetermine if the big table is partitioned on a column whose partitionscheme can be hash partitioning or range partitioning. The server thendetermines whether the join predicate corresponds with the partitionedcolumn. This step ensures that partitions that do not match the dynamicfilters are not pushed down. If the server determines that the joinpredicate is not a partitioned column of the big table, the server willthen execute step 418, in which the server pushes down all of thedynamic filters. For example, the server may pushdown the dynamicfilters acquired in step 402 to the big table scanning on the probe sideof join. This ensures that only the columns of the big table thatcorrespond with the dynamic filters are scanned, thereby reducinginput/output costs.

On the other hand, if the server determines that the join predicate is apartitioned column of the big table at step 416, the server can thenexecute step 420. In step 420, the server partitions the dynamic filtersusing the same partition scheme as the big table, prunes the big tablepartitions, and pushes down the dynamic filters. For example, thedynamic filters acquired in step 402 are partitioned using the samescheme as the big table. In some embodiments, the big table may bepartitioned into different physical nodes where each physical nodecontains different rows. The dynamic filters would thereby bepartitioned according to this same scheme. The server then prunes thebig table partitions that do not correspond with any of the dynamicfilters. Finally, the server pushes down the dynamic filters to the bigtable scanning on the probe side of join. By further reducing the amountof tuples scanned in the big table, the input/output cost of the entirehash join or semi-join operation is reduced.

FIG. 5 is a flowchart of an exemplary method 500 for determining theselectivity of the big table, consistent with some embodiments of thisdisclosure. The exemplary method 500 may be performed by a server (e.g.,server 202 of FIG. 2 or compute node 304 of FIG. 3).

In step 502, the server determines the selectivity of the big tablebased on the statistical information and the dynamic filters. Forexample, the server may determine the percentage of the big table to beread for the present query based on the statistical information cachedin the TableStatisticsCache of FIG. 3 and based on the dynamic filtersacquired during the hash table building in computer node 304 of FIG. 3.

In step 504, the server determines whether selectivity satisfies athreshold condition. For example, the server may determine whether theselectivity of the big table determined in step 406 is less than 20%,where 20% represents the percent of the big table to be read. In someembodiments, the threshold condition is prestored in memory, such as aconfiguration variable stored in one of the one or more storage devices206 of FIG. 2. If the server determines that the selectivity does notsatisfy the threshold condition, the server can then execute step 506,in which the server continues with a regular hash join or semi-joinoperation. For example, the server may not pushdown any dynamic filtersand instead continue probing and then scanning the big table.

On the other hand, if the server determines that the selectivitysatisfies the threshold condition, the server can then execute step 508.In step 508, the server passes the filters to big table scanning on theprobe side of join. For example, the server may pushdown the dynamicfilters from the small table to the big table in order to reduce thenumber of tuples fetched and thereby reduce input/output cost.

FIG. 6 is a flowchart of an exemplary method 600 for dynamic filterpushdown for massive parallel processing databases on cloud, consistentwith some embodiments of this disclosure. The exemplary method 600 maybe performed by a server (e.g., server 202 of FIG. 2 or compute node 304of FIG. 3).

In step 602, the server prunes partitions of the big table if there isno matching dynamic filter. For example, the server may determinewhether big table is partitioned on a column, whose partition scheme maybe hash partitioning or range partitioning. The server may thenpartition the dynamic filters according to the same partitioning schemeas the big table. If there is no matching dynamic filter for a givenpartition of the big table, then the server may prune that partition ofthe big table.

In step 604, the server pushes down the dynamic filters to the big tablescanning. For example, the server may pushdown the dynamic filtersacquired during the hash table building in computer node 304 of FIG. 3to the big table scanning on the probe side of join.

FIG. 7 is a flowchart of an exemplary method 700 for determining themethod for dynamic filter pushdown, consistent with some embodiments ofthis disclosure. The exemplary method 700 may be performed by a server(e.g., server 202 of FIG. 2 or compute node 304 of FIG. 3).

In step 702, the server determines a storage type of the big table ofthe heterogeneous storage types. For example, the big table may bestored on various different types of storages such as OSS, HBase,TableStore, PostgreSQL, and MySQL. The server may determine which ofthese storage types the big table is stored on.

In step 704, the server chooses the API for tuple fetching according tothe storage type. For example, the server may determine the API fortuple fetching corresponding to the storage type of the big tabledetermined in step 702. In some embodiments, such as when the storagetype of the big table is MySQL or PostgreSQL, the server may use SQLwith in list instead of an API for tuple fetching.

It should be noted that, the relational terms herein such as “first” and“second” are used only to differentiate an entity or operation fromanother entity or operation, and do not require or imply any actualrelationship or sequence between these entities or operations. Moreover,the words “comprising,” “having,” “containing,” and “including,” andother similar forms are intended to be equivalent in meaning and be openended in that an item or items following any one of these words is notmeant to be an exhaustive listing of such item or items, or meant to belimited to only the listed item or items.

As used herein, unless specifically stated otherwise, the term “or”encompasses all possible combinations, except where infeasible. Forexample, if it is stated that a database may include A or B, then,unless specifically stated otherwise or infeasible, the database mayinclude A, or B, or A and B. As a second example, if it is stated that adatabase may include A, B, or C, then, unless specifically statedotherwise or infeasible, the database may include A, or B, or C, or Aand B, or A and C, or B and C, or A and B and C.

It is appreciated that the above described embodiments can beimplemented by hardware, or software (program codes), or a combinationof hardware and software. If implemented by software, it may be storedin the above-described computer-readable media. The software, whenexecuted by the processor can perform the disclosed methods. Thecomputing units and other functional units described in this disclosurecan be implemented by hardware, or software, or a combination ofhardware and software. One of ordinary skill in the art will alsounderstand that multiple ones of the above described modules/units maybe combined as one module/unit, and each of the above describedmodules/units may be further divided into a plurality ofsub-modules/sub-units.

In the foregoing specification, embodiments have been described withreference to numerous specific details that can vary from implementationto implementation. Certain adaptations and modifications of thedescribed embodiments can be made. Other embodiments can be apparent tothose skilled in the art from consideration of the specification andpractice of the invention disclosed herein. It is intended that thespecification and examples be considered as exemplary only, with a truescope and spirit of the invention being indicated by the followingclaims. It is also intended that the sequence of steps shown in figuresare only for illustrative purposes and are not intended to be limited toany particular sequence of steps. As such, those skilled in the art canappreciate that these steps can be performed in a different order whileimplementing the same method. In the drawings and specification, therehave been disclosed exemplary embodiments. However, many variations andmodifications can be made to these embodiments. Accordingly, althoughspecific terms are employed, they are used in a generic and descriptivesense only and not for purposes of limitation, the scope of theembodiments being defined by the following claims.

1. A method comprising: acquiring one or more filters corresponding to aquery; acquiring statistics information of one or more database tables;determining a selectivity of the one or more database tables based onthe statistics information; determining whether the selectivitysatisfies a threshold condition; and pushing down the one or morefilters to the one or more database tables based on the determination ofwhether the selectivity satisfies the threshold condition.
 2. The methodof claim 1, wherein the one or more database tables correspond with oneof one or more heterogeneous storage types further comprising:determining a storage type of the one or more heterogeneous storagetypes based on the one or more database tables; and wherein pushing downthe one or more filters to the one or more database tables is based onthe determination of the storage type.
 3. The method of claim 1, furthercomprising: determining whether to partition the one or more filtersbased on the query and the one or more database tables; partitioning theone or more filters based on the one or more database tables; pruningpartitions of the one or more database tables based on the one or morepartitioned filters; and wherein the pushing down the one or morefilters to the one or more database tables comprises pushing down theone or more partitioned filters to the one or more database tables. 4.The method of claim 2, wherein pushing down the one or more filters tothe one or more database tables includes pushing down the one of morefilters using an application programming interface.
 5. The method ofclaim 1, wherein the statistics information is periodically cached basedon the one or more database tables before being acquired.
 6. The methodof claim 1, wherein the query includes a query for a join or semi joinoperation.
 7. The method of claim 1, wherein the one or more databasetables includes a big table.
 8. A device comprising: a memory configuredto store a set of instructions; and a processor configured to executethe set of instructions to cause the device to: acquire one or morefilters corresponding to a query; acquire statistics information of oneor more database tables; determine a selectivity of the one or moredatabase tables based on the statistics information; determine whetherthe selectivity satisfies a threshold condition; push down the one ormore filters to the one or more database tables based on thedetermination of whether the selectivity satisfies the thresholdcondition.
 9. The device of claim 8, wherein the one or more databasetables correspond with one of one or more heterogeneous storage typesand wherein the processor is further configured to execute the set ofinstructions to cause the device to: determine a storage type of the oneor more heterogeneous storage types based on the one or more databasetables; and wherein the pushing down the one or more filters to the oneor more database tables is based on the determination of the storagetype.
 10. The device of claim 8, wherein the processor is furtherconfigured to execute the set of instructions to cause the device to:determine whether to partition the one or more filters based on thequery and the one or more database tables; partition the one or morefilters based on the one or more database tables; prune partitions ofthe one or more database tables based on the one or more partitionedfilters; and wherein the pushing down the one or more filters to the oneor more database tables comprises pushing down the one or morepartitioned filters to the one or more database tables.
 11. The deviceof claim 9, wherein the pushing down the one or more filters to the oneor more database tables includes pushing down the one of more filtersusing an application programming interface.
 12. The device of claim 8,wherein the statistics information is periodically cached based on theone or more database tables before being acquired.
 13. The device ofclaim 8, wherein the query includes a query for a join or semi joinoperation.
 14. The device of claim 8, wherein the one or more databasetables includes a big table.
 15. A database system, comprising: at leastone front node configured to receive one or more query associated withone or more filters; at least one compute node coupled to the at leastone front node, the at least one compute node configured to: acquire oneor more filters corresponding to a query; acquire statistics informationof one or more database tables; determine a selectivity of the one ormore database tables based on the statistics information; determinewhether the selectivity satisfies a threshold condition; and push downthe one or more filters to the one or more database tables based on thedetermination of whether the selectivity satisfies the thresholdcondition.
 16. The database system according to claim 15, wherein theone or more database tables correspond with one of one or moreheterogeneous storage types and wherein the compute node is furtherconfigured to: determine a storage type of the one or more heterogeneousstorage types based on the one or more database tables; and wherein thepushing down the one or more filters to the one or more database tablesis based on the determination of the storage type.
 17. The databasesystem according to claim 15, wherein the compute node is furtherconfigured to: determine whether to partition the one or more filtersbased on the query and the one or more database tables; partition theone or more filters based on the one or more database tables; prunepartitions of the one or more database tables based on the one or morepartitioned filters; and wherein the pushing down the one or morefilters to the one or more database tables comprises pushing down theone or more partitioned filters to the one or more database tables. 18.The database system according to claim 16, wherein the pushing down theone or more filters to the one or more database tables includes pushingdown the one of more filters using an application programming interface.19. The database system according to claim 15, wherein the statisticsinformation is periodically cached based on the one or more databasetables before being acquired.
 20. The database system according to claim15, wherein the query includes a query for a join or semi-joinoperation.
 21. The computer readable medium according to claim 15,wherein the one or more database tables includes a big table.